﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_ReceiveDtls : IDAL_FIM_ReceiveDtls
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_FIM_ReceiveDtls()
        {

        }

        public IList<M_FIM_ReceiveDtls> GetFull(Nullable<int> receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_ReceiveDtls> infoList = new List<M_FIM_ReceiveDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@receive_seq", receive_seq);

                strSQL = "SELECT rd.receive_dtls_seq, rd.receive_seq, rd.receive_type_seq"
                    + ", rd.receive_dtls_name, rd.amount, rd.currency_seq"
                    + ", rd.exchange_rate, rd.bath_amount"
                    + ", cur.currency_name, rd.record_status"
                    + ", rd.created_by, rd.created_when, rd.updated_by, rd.updated_when"
                    + " FROM fim_receive_dtls rd"
                    + " LEFT JOIN fim_mas_currencies cur ON rd.currency_seq = cur.currency_seq"
                    + " WHERE rd.record_status not like @delete_status"
                    + " AND rd.receive_seq = @receive_seq"
                    + " ORDER BY rd.receive_dtls_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_ReceiveDtls info = new M_FIM_ReceiveDtls();
                        info.receive_dtls_seq = Convert.ToInt32(drc[i]["receive_dtls_seq"]);
                        info.receive_seq = cn.ConvertNullInt(drc[i]["receive_seq"]);
                        info.receive_dtls_name = drc[i]["receive_dtls_name"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.currency_seq = cn.ConvertNullInt(drc[i]["currency_seq"]);
                        info.exchange_rate = cn.ConvertNullDouble(drc[i]["exchange_rate"]);
                        info.bath_amount = cn.ConvertNullDouble(drc[i]["bath_amount"]);
                        info.currency_name = drc[i]["currency_name"].ToString();
                        info.receive_type_seq = cn.ConvertNullInt(drc[i]["receive_type_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_ReceiveDtls> GetList(Nullable<int> receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_ReceiveDtls> infoList = new List<M_FIM_ReceiveDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@receive_seq", receive_seq);

                strSQL = "SELECT rd.receive_dtls_seq, rd.receive_dtls_name"
                    + " FROM fim_receive_dtls rd"
                    + " WHERE rd.record_status not like @delete_status"
                    + " AND rd.receive_seq = @receive_seq"
                    + " ORDER BY rd.receive_dtls_seq";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_ReceiveDtls info = new M_FIM_ReceiveDtls();
                        info.receive_dtls_seq = Convert.ToInt32(drc[i]["receive_dtls_seq"]);
                        info.receive_dtls_name = drc[i]["receive_dtls_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_ReceiveDtls GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_ReceiveDtls info = new M_FIM_ReceiveDtls();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT rd.receive_dtls_seq, rd.receive_seq, rd.receive_type_seq"
                    + ", rd.receive_dtls_name, rd.amount, rd.currency_seq"
                    + ", rd.exchange_rate, rd.bath_amount"
                    + ", cur.currency_name, rd.record_status"
                    + ", rd.created_by, rd.created_when, rd.updated_by, rd.updated_when"
                    + " FROM fim_receive_dtls rd"
                    + " LEFT JOIN fim_mas_currencies cur ON rd.currency_seq = cur.currency_seq"
                    + " WHERE rd.receive_dtls_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.receive_dtls_seq = Convert.ToInt32(drc[0]["receive_dtls_seq"]);
                    info.receive_seq = cn.ConvertNullInt(drc[0]["receive_seq"]);
                    info.receive_dtls_name = drc[0]["receive_dtls_name"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.currency_seq = cn.ConvertNullInt(drc[0]["currency_seq"]);
                    info.exchange_rate = cn.ConvertNullDouble(drc[0]["exchange_rate"]);
                    info.bath_amount = cn.ConvertNullDouble(drc[0]["bath_amount"]);
                    info.currency_name = drc[0]["currency_name"].ToString();
                    info.receive_type_seq = cn.ConvertNullInt(drc[0]["receive_type_seq"]);

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public DataTable GetReceiveDtlsAndMoneyRequestDtls(int money_req_seq, int? receive_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(2, "@receive_seq", receive_seq);

                if (receive_seq.HasValue)
                {
                    strSQL = @"
SELECT mrd.money_req_dtls_seq, rd.receive_dtls_seq
, rd.receive_type_seq
, rd.receive_seq
, exp.bgm_exp_name
, coalesce(mrd.amount, 0) as amount
, coalesce(ret.amount, 0) + coalesce(r.amount, 0) as amount_return
, rd.amount as amount_receive

FROM fim_money_request_dtls mrd
LEFT JOIN bgm_project_operation_expense ope on mrd.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
LEFT JOIN bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
LEFT JOIN bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq
left join fim_receive_dtls rd on mrd.money_req_dtls_seq = rd.receive_type_seq and rd.receive_seq = @receive_seq
left join (select retd.money_req_dtls_seq, sum(retd.amount) as amount
    from fim_money_return_dtls retd
    left join fim_money_returns ret on retd.return_seq = ret.return_seq
    where retd.record_status <> 'D'
    and ret.record_status = 'A'
    group by retd.money_req_dtls_seq
    ) ret on mrd.money_req_dtls_seq = ret.money_req_dtls_seq
left join (select rd.receive_type_seq, sum(rd.amount) as amount
    from fim_receive_dtls rd
    left join fim_receives r on rd.receive_seq = r.receive_seq
    where rd.record_status <> 'D'
    and r.record_status not in ('D', 'C')
    and r.receive_seq <> @receive_seq
    group by rd.receive_type_seq
    ) r on mrd.money_req_dtls_seq = r.receive_type_seq
WHERE mrd.money_req_seq = @money_req_seq
and mrd.money_req_seq <> @money_req_seq
and rd.record_status <> @delete_status
ORDER BY mrd.money_req_dtls_seq
";
                }
                else
                {
                    strSQL = @"
SELECT mrd.money_req_dtls_seq, null as receive_dtls_seq
, mrd.money_req_dtls_seq as receive_type_seq
, null as receive_seq
, exp.bgm_exp_name
, coalesce(mrd.amount, 0) as amount
, coalesce(ret.amount, 0) + coalesce(r.amount, 0) as amount_return
, 0 as amount_receive

FROM fim_money_request_dtls mrd
LEFT JOIN bgm_project_operation_expense ope on mrd.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
LEFT JOIN bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
LEFT JOIN bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq
left join (select retd.money_req_dtls_seq, sum(retd.amount) as amount
    from fim_money_return_dtls retd
    left join fim_money_returns ret on retd.return_seq = ret.return_seq
    where retd.record_status <> 'D'
    and ret.record_status = 'A'
    group by retd.money_req_dtls_seq
    ) ret on mrd.money_req_dtls_seq = ret.money_req_dtls_seq
left join (select rd.receive_type_seq, sum(rd.amount) as amount
    from fim_receive_dtls rd
    left join fim_receives r on rd.receive_seq = r.receive_seq
    where rd.record_status <> 'D'
    and r.record_status not in ('D', 'C')
    group by rd.receive_type_seq
    ) r on mrd.money_req_dtls_seq = r.receive_type_seq
WHERE mrd.money_req_seq = @money_req_seq
and mrd.record_status <> @delete_status
ORDER BY mrd.money_req_dtls_seq
";
                }

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(M_FIM_ReceiveDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@receive_dtls_seq", info.receive_dtls_seq);
                dbManager.AddParameters(1, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(2, "@receive_dtls_name", info.receive_dtls_name);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@currency_seq", info.currency_seq);
                dbManager.AddParameters(5, "@exchange_rate", info.exchange_rate);
                dbManager.AddParameters(6, "@bath_amount", info.bath_amount);

                dbManager.AddParameters(7, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(8, "@created_by", info.created_by);
                dbManager.AddParameters(9, "@created_when", info.created_when);
                dbManager.AddParameters(10, "@receive_type_seq", info.receive_type_seq);

                strSQL = "INSERT INTO fim_receive_dtls"
                    + "(receive_seq, receive_dtls_name, amount"
                    + ", currency_seq, exchange_rate, bath_amount"
                    + ", record_status, created_by, created_when, receive_type_seq)"
                    + " VALUES (@receive_seq"
                        + ", @receive_dtls_name"
                        + ", @amount"
                        + ", @currency_seq"
                        + ", @exchange_rate"
                        + ", @bath_amount"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ", @receive_type_seq"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_ReceiveDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@receive_dtls_seq", info.receive_dtls_seq);
                dbManager.AddParameters(1, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(2, "@receive_dtls_name", info.receive_dtls_name);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@currency_seq", info.currency_seq);
                dbManager.AddParameters(5, "@exchange_rate", info.exchange_rate);
                dbManager.AddParameters(6, "@bath_amount", info.bath_amount);

                dbManager.AddParameters(7, "@updated_by", info.updated_by);
                dbManager.AddParameters(8, "@updated_when", info.updated_when);
                dbManager.AddParameters(9, "@receive_type_seq", info.receive_type_seq);

                strSQL = "UPDATE fim_receive_dtls"
                    + " SET receive_seq = @receive_seq"
                    + ", receive_dtls_name = @receive_dtls_name"
                    + ", amount = @amount"
                    + ", currency_seq = @currency_seq"
                    + ", exchange_rate = @exchange_rate"
                    + ", bath_amount = @bath_amount"
                    + ", receive_type_seq = @receive_type_seq"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_dtls_seq = @receive_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_ReceiveDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@receive_dtls_seq", info.receive_dtls_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_receive_dtls"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_dtls_seq = @receive_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
